Funny Developer Tricks – upper(number)
I saw a funny one today. I’ll paraphrase:
select * from table_x
where upper(acct_number) = '876876'
or upper(acct_number) = '826531';
Nice huh?
1. Obviously turns off any indexes on acct_number (unless they had a function based index).
2. Looks like they are probably storing numeric data in a character data type (or implicitly converting a number to character string).
3. Not using bind variables so they are not helping themselves from a parsing standpoint.
4. Finally, they’re making darn sure they take care of any mixed case numbers!
Some fun.
(by the way, those lower case numbers cause me problems all the time)
I saw that trick many times when developers want to build their SQL statement “dynamically” with separate codepieces for “from”, “where”, “order by” etc. The last example was otrs (otrs.org), they are using the same 9000 lines perl function to build a select to search through several “key” columns of ticket table (look at the schema it has lot of fun inside too ftp://ftp.otrs.org/pub/otrs/misc/otrs-2.4-database.png),so they have to use the ugly lower(number) trick because they cannot be sure of the datatype of their argument.
Just found this great idea:
Two occurrences of ‘where column is null’ in the one statement forcing a double full table scan of a 7 million row table.
The problem? EVERY value in the table is NULL!!!
I’d like to see where upper(acct_number) = ‘x876876a’ 🙂
But seriously, isn’t it normal for a generic system to allow characters in “account numbers?” I know you aren’t going to publish every detail of this system, that wouldn’t be funny, but “probably” causes me all sorts of grief with sloppy business analysis in my daily life, right up there with that guy “Will Never.”
Hey Joel,
I didn’t actually check to see if they had any records with non-numeric data. Mayne I’ll go back and check that. As you say, it’s just an every day event.
Michael,
That one must be embedded in a view (or two or three) to get 2 full table scans on the same table.
Kerry